﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace NxERP.Bll
{
    public class SqlHelper
    {
        private string _dbconn = string.Empty;

        /// <summary>
        /// 返回SqlHelper的单例实例
        /// </summary>
        /// <param name="dbConn"> 数据库连接字符串 </param>
        /// <returns> SqlHelper实例 </returns>
        public SqlHelper(string dbConn)
        {
            _dbconn = dbConn;
        }

        //public SqlHelper(string dbConn)
        //{
        //    _dbconn = dbConn;
        //}
        public int ExcuteProc(string procName, out Dictionary<string, string> outParameter, SqlParameter[] sqlParameter = null)
        {
            outParameter = new Dictionary<string, string>();
            using (SqlConnection conn = new SqlConnection(_dbconn))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure };
                if (sqlParameter != null)
                {
                    command.Parameters.AddRange(sqlParameter);
                }
                int rt = command.ExecuteNonQuery();
                if (outParameter != null)
                {
                    foreach (SqlParameter item in sqlParameter)
                    {
                        if (item.Direction == ParameterDirection.Output)
                        {
                            outParameter.Add(item.ParameterName, item.Value.ToString());
                        }
                    }
                }
                command.Parameters.Clear();
                return rt;
            }
        }

        public int ExcuteSql(List<string> sqlList, SqlParameter[] sqlParameter = null)
        {
            if (sqlList.Count > 0)
            {
                return ExcuteSql(string.Join(";", sqlList), sqlParameter);
            }
            return 0;
        }

        public int ExcuteSql(string sql, SqlParameter[] sqlParameter = null)
        {
            //手工创建连接字符串
            //SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder
            //{
            //    DataSource = ".",
            //    InitialCatalog = "NxCMS_db",
            //    UserID = "sa",
            //    Password = "9128425",
            //    Pooling = true,
            //    MaxPoolSize = 20
            //};
            using (SqlConnection conn = new SqlConnection(_dbconn))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn) { CommandType = CommandType.Text };
                if (sqlParameter != null)
                {
                    command.Parameters.AddRange(sqlParameter);
                }
                int _rt = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return _rt;
            }
        }

        public int GetCount(string tableName, string where, SqlParameter[] sqlParameter = null)
        {
            using (SqlConnection conn = new SqlConnection(_dbconn))
            {
                string SearchSQL = "";
                if (!string.IsNullOrEmpty(where))
                {
                    SearchSQL = "SELECT COUNT(1) FROM " + tableName + " WHERE " + where;
                }
                else
                {
                    SearchSQL = "SELECT COUNT(1) FROM";
                }
                conn.Open();
                SqlCommand command = new SqlCommand(SearchSQL, conn) { CommandType = CommandType.Text };
                if (sqlParameter != null)
                {
                    command.Parameters.AddRange(sqlParameter);
                }
                int _rt = int.Parse(command.ExecuteScalar().ToString());
                command.Parameters.Clear();
                return _rt;
            }
        }

        /// <summary>
        /// 获取非重复Count行数
        /// </summary>
        /// <param name="tableName">     </param>
        /// <param name="DistinctField"> </param>
        /// <param name="where">         </param>
        /// <param name="sqlParameter">  </param>
        /// <returns> </returns>
        public int GetDistinctCount(string tableName, string DistinctField, string where, SqlParameter[] sqlParameter = null)
        {
            using (SqlConnection conn = new SqlConnection(_dbconn))
            {
                string SearchSQL = "";
                if (!string.IsNullOrEmpty(where))
                {
                    SearchSQL = $"SELECT COUNT(DISTINCT {DistinctField}) FROM " + tableName + " WHERE " + where;
                }
                else
                {
                    SearchSQL = $"SELECT COUNT((DISTINCT {DistinctField}) FROM";
                }
                conn.Open();
                SqlCommand command = new SqlCommand(SearchSQL, conn) { CommandType = CommandType.Text };
                if (sqlParameter != null)
                {
                    command.Parameters.AddRange(sqlParameter);
                }
                int _rt = int.Parse(command.ExecuteScalar().ToString());
                command.Parameters.Clear();
                return _rt;
            }
        }

        public DataTable GetDataTableFromSql(string sql, string where = null, Dictionary<string, string> sortColumn = null, SqlParameter[] sqlParameter = null)
        {
            if (string.IsNullOrEmpty(sql))
            {
                return null;
            }
            if (!string.IsNullOrEmpty(where))
            {
                sql = sql + " WHERE " + where;
            }
            if (sortColumn != null)
            {
                List<string> _orderby = new List<string>();
                foreach (KeyValuePair<string, string> item in sortColumn)
                {
                    _orderby.Add(item.Key + "  " + item.Value);
                }
                sql = sql + " ORDER BY " + string.Join(",", _orderby);
            }
            using (SqlConnection conn = new SqlConnection(_dbconn))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn) { CommandType = CommandType.Text };
                if (sqlParameter != null)
                {
                    command.Parameters.AddRange(sqlParameter);
                }
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                da.Fill(dt);
                command.Parameters.Clear();
                return dt;
            }
        }

        public DataTable GetDataTableFromTableName(string tableName, string[] selectColumn = null, string where = null, Dictionary<string, string> sortColumn = null, SqlParameter[] sqlParameter = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            string sql = "";
            if (selectColumn != null && selectColumn.Length > 0)
            {
                sql = "SELECT " + string.Join(",", selectColumn) + " FROM " + tableName.Trim();
            }
            else
            {
                sql = "SELECT * FROM " + tableName;
            }
            if (!string.IsNullOrEmpty(where))
            {
                sql = sql + " WHERE " + where;
            }
            if (sortColumn != null)
            {
                List<string> _orderby = new List<string>();
                foreach (KeyValuePair<string, string> item in sortColumn)
                {
                    _orderby.Add(item.Key + "  " + item.Value);
                }
                sql = sql + " ORDER BY " + string.Join(",", _orderby);
            }
            using (SqlConnection conn = new SqlConnection(_dbconn))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn) { CommandType = CommandType.Text };
                if (sqlParameter != null)
                {
                    command.Parameters.AddRange(sqlParameter);
                }
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                da.Fill(dt);
                command.Parameters.Clear();
                return dt;
            }
        }

        public string GetColumnValue(string tableName, string dbColumn, string where, SqlParameter[] sqlParameter = null)
        {
            if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(dbColumn))
            {
                string sql = "SELECT TOP 1 " + dbColumn + " FROM " + tableName + " WHERE " + where;
                if (string.IsNullOrEmpty(where))
                {
                    sql = "SELECT TOP 1" + dbColumn + " FROM " + tableName;
                }
                using (SqlConnection conn = new SqlConnection(_dbconn))
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand(sql, conn);
                    if (sqlParameter != null)
                    {
                        command.Parameters.AddRange(sqlParameter);
                    }
                    object objResult = command.ExecuteScalar();
                    command.Parameters.Clear();
                    if (objResult != null && !Convert.IsDBNull(objResult))
                    {
                        return objResult.ToString();
                    }
                    else
                    {
                        return "";
                    }
                }
            }
            else
            {
                return "";
            }
        }

        public int GetMaxID(string tableName, string dbColumn, string where = null, SqlParameter[] sqlParameter = null)
        {
            if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(dbColumn))
            {
                string sql = "SELECT MAX(" + dbColumn + ")+1 FROM " + tableName;
                if (!string.IsNullOrEmpty(where))
                {
                    sql = sql + " WHERE " + where;
                }
                using (SqlConnection conn = new SqlConnection(_dbconn))
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand(sql, conn);
                    if (sqlParameter != null)
                    {
                        command.Parameters.AddRange(sqlParameter);
                    }
                    object objResult = command.ExecuteScalar();
                    command.Parameters.Clear();
                    if (!Convert.IsDBNull(objResult))
                    {
                        return int.Parse(objResult.ToString());
                    }
                    else
                    {
                        return 1;
                    }
                }
            }
            else
            {
                return 1;
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">    表名 </param>
        /// <param name="where">        删除条件，不能为空或者空字符串 </param>
        /// <param name="sqlParameter"> </param>
        /// <returns> </returns>
        public int Del(string tableName, string where, SqlParameter[] sqlParameter = null)
        {
            if (string.IsNullOrEmpty(where))
            {
                return -1;
            }
            else
            {
                string sql = "DELETE " + tableName + " WHERE " + where;
                return ExcuteSql(sql, sqlParameter);
            }
        }

        public int Inset(string tableName, Dictionary<string, string> insertColumn, SqlParameter[] sqlParameter = null)
        {
            if (insertColumn == null || string.IsNullOrEmpty(tableName))
            {
                return -1;
            }
            string sql = "";
            List<string> _keys = new List<string>();
            List<string> _values = new List<string>();
            foreach (var item in insertColumn)
            {
                _keys.Add(item.Key);
                if (IsNumeric(item.Value))
                {
                    _values.Add(item.Value);
                }
                else
                {
                    _values.Add("'" + item.Value + "'");
                }
            }
            sql = "INSERT INTO " + tableName + "(" + string.Join(",", _keys.ToArray()) + ") VALUES(" + string.Join(",", _values.ToArray()) + ")";
            return ExcuteSql(sql, sqlParameter);
        }

        public int Update(string tableName, Dictionary<string, string> upColumn, string where, SqlParameter[] sqlParameter = null)
        {
            if (string.IsNullOrEmpty(where) || upColumn == null)
            {
                return -1;
            }
            List<string> _temp = new List<string>();
            foreach (var item in upColumn)
            {
                if (IsNumeric(item.Value))
                {
                    _temp.Add(item.Key + "=" + item.Value);
                }
                else
                {
                    _temp.Add(item.Key + "='" + item.Value + "'");
                }
            }
            string sql = "UPDATE " + tableName + " SET " + string.Join(",", _temp) + " WHERE " + where;
            return ExcuteSql(sql, sqlParameter);
        }

        /// <summary>
        /// 获取含有sql统计函数的结果
        /// </summary>
        /// <param name="tableName">    表明 </param>
        /// <param name="sqlFun">       sql统计函数名 </param>
        /// <param name="dbColumn">     要统计的字段，该列必须是数据库可统计的数据类型 </param>
        /// <param name="where">        条件 </param>
        /// <param name="sqlParameter"> 参数 </param>
        /// <returns> </returns>
        public decimal GetStatistics(string tableName, string sqlFun, string dbColumn, string where = null, SqlParameter[] sqlParameter = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                return 0;
            }
            if (tableName.Substring(0, 1) != "[")
            {
                tableName = "[" + tableName + "]";
            }
            using (SqlConnection conn = new SqlConnection(_dbconn))
            {
                string SearchSQL = "";
                if (!string.IsNullOrEmpty(where))
                {
                    SearchSQL = $"SELECT {sqlFun}({dbColumn}) FROM " + tableName + " WHERE " + where;
                }
                else
                {
                    SearchSQL = $"SELECT {sqlFun}({dbColumn}) FROM " + tableName;
                }
                conn.Open();
                SqlCommand command = new SqlCommand(SearchSQL, conn) { CommandType = CommandType.Text };
                if (sqlParameter != null)
                {
                    command.Parameters.AddRange(sqlParameter);
                }
                string r = command.ExecuteScalar().ToString();
                decimal.TryParse(command.ExecuteScalar().ToString(), out decimal _rt);
                command.Parameters.Clear();
                return _rt;
            }
        }

        /// <summary>
        /// 创建SQL参数
        /// </summary>
        /// <param name="parameterName">      参数名称，不需要@ </param>
        /// <param name="parameterValue">     参数值 </param>
        /// <param name="sqlDbType">          参数类型 </param>
        /// <param name="parameterDirection"> 参数描述，输入还是输出 </param>
        /// <returns> </returns>
        public SqlParameter CreatSqlParameter(string parameterName, object parameterValue, SqlDbType sqlDbType, ParameterDirection parameterDirection)
        {
            if (parameterName.Substring(0, 1) != "@")
            {
                parameterName = "@" + parameterName;
            }
            return new SqlParameter()
            {
                ParameterName = parameterName,
                Value = parameterValue,
                SqlDbType = sqlDbType,
                Direction = parameterDirection
            };
        }

        public static bool IsNumeric(string value)
        {
            return Regex.IsMatch(value, @"^[+-]?/d*[.]?/d*$");
        }
    }
}